/*
 * Copyright (C) 2008 Google Inc.
 * 
 * Licensed under the Apache License, Version 2.0 (the "License"); you may not
 * use this file except in compliance with the License. You may obtain a copy of
 * the License at
 * 
 * http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
 * WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
 * License for the specific language governing permissions and limitations under
 * the License.
 */

package se.su.dsv.theclient;

import java.util.Iterator;

import theserver.DeviceInfo;
import theserver.Venue;
import theserver.VenueActivity;
import theserver.VenueList;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import android.view.View.OnCreateContextMenuListener;

/**
 * Simple notes database access helper class. Defines the basic CRUD operations
 * for the notepad example, and gives the ability to list all notes as well as
 * retrieve or modify a specific note.
 * 
 * This has been improved from the first version of this tutorial through the
 * addition of better error handling and also using returning a Cursor instead
 * of using a collection of inner classes (which is less scalable and not
 * recommended).
 */
public class ClientDBAdapter {

    //public static final String KEY_TITLE = "title";
    //public static final String KEY_BODY = "body";
    public static final String KEY_ROWID = "_id";
    
    //public static final String KEY_DEVICE_ROWID = "_id";
    public static final String KEY_DEVICE_ID = "deviceid";
    public static final String KEY_DEVICE_OWNER = "owner";
    public static final String KEY_DEVICE_IP = "ip";
    public static final String KEY_DEVICE_PORT = "port";
    public static final String KEY_DEVICE_LASTSEEN = "lastseen";
    public static final String KEY_DEVICE_LOCATION = "location";

    //public static final String KEY_VENUE_ROWID = "_id";
    public static final String KEY_VENUE_ID = "venueid";
    public static final String KEY_VENUE_NAME = "venuename";
    public static final String KEY_VENUE_ADDRESS = "venueaddress";
    public static final String KEY_VENUE_ZIP = "venuezip";
    public static final String KEY_VENUE_TOWN = "venuetown";
    public static final String KEY_VENUE_PHONE = "venuephone";

    //public static final String KEY_ACTIVITY_ROWID = "_id";
    public static final String KEY_ACTIVITY_VENUEID = "venueid";
    public static final String KEY_ACTIVITY_NAME = "activity";
    public static final String KEY_ACTIVITY_MINPARTICIPANTS = "minparticipants";
    public static final String KEY_ACTIVITY_MAXPARTICIPANTS = "maxparticipants";

    private static final String TAG = "NotesDbAdapter";
    private DatabaseHelper mDbHelper;
    private SQLiteDatabase mDb;
    
    /**
     * Database creation sql statement
     */
    private static final String DATABASE_CREATE_DEVICES =
            "create table device (_id integer primary key autoincrement, " +
            	"deviceid text not null," +
                "owner text not null," +
                "ip text not null," +
                "port integer not null," +
                "lastseen timestamp not null," +
                "location integer);";
    private static final String DATABASE_CREATE_VENUES = 
    		"create table venue (_id integer primary key autoincrement, " +
            	"venueid integer auto_increment ," +
            	"venuename text not null," +
            	"venueaddress text," +
            	"venuezip integer," +
            	"venuetown text not null," +
            	"venuephone text);";
    private static final String DATABASE_CREATE_ACTIVITIES = 
    		"create table venueactivity (_id integer primary key autoincrement, " +
            	"venueid text," +
            	"activity text," +
            	"minparticipants integer default 0," +
            	"maxparticipants integer default 0);";
    private static final String DATABASE_CREATE = DATABASE_CREATE_DEVICES+
    	DATABASE_CREATE_VENUES+
    	DATABASE_CREATE_ACTIVITIES;

    private static final String DATABASE_NAME = "social";
    public static final String DATABASE_TABLE_DEVICE = "device";
    public static final String DATABASE_TABLE_VENUE = "venue";
    public static final String DATABASE_TABLE_ACTIVITY = "venueactivity";
    private static final int DATABASE_VERSION = 1;

    private final Context mCtx;

    private static class DatabaseHelper extends SQLiteOpenHelper {

        DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {

            db.execSQL(DATABASE_CREATE);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                    + newVersion + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS device;" +
            		" DROP TABLE IF EXISTS venue;" +
            		" DROP TABLE IF EXISTS venueactivity;");
            onCreate(db);
        }
    }

    /**
     * Constructor - takes the context to allow the database to be
     * opened/created
     * 
     * @param ctx the Context within which to work
     */
    public ClientDBAdapter(Context ctx) {
        this.mCtx = ctx;
    }

    public void kill() {
        mDb.execSQL("DROP TABLE IF EXISTS device;" +
        		" DROP TABLE IF EXISTS venue;" +
        		" DROP TABLE IF EXISTS venueactivity;");
        mDbHelper.onCreate(mDb);
    }
    
    /**
     * Open the notes database. If it cannot be opened, try to create a new
     * instance of the database. If it cannot be created, throw an exception to
     * signal the failure
     * 
     * @return this (self reference, allowing this to be chained in an
     *         initialization call)
     * @throws SQLException if the database could be neither opened or created
     */
    public ClientDBAdapter open() throws SQLException {
        mDbHelper = new DatabaseHelper(mCtx);
        mDb = mDbHelper.getWritableDatabase();
        return this;
    }
    
    public void close() {
        mDbHelper.close();
    }


    /**
     * Create a new note using the title and body provided. If the note is
     * successfully created return the new rowId for that note, otherwise return
     * a -1 to indicate failure.
     * 
     * @param title the title of the note
     * @param body the body of the note
     * @return rowId or -1 if failed
     */
    public long createDevice(DeviceInfo device) {
        ContentValues values = new ContentValues();
        values.put(KEY_DEVICE_ID, device.getDeviceID());
        values.put(KEY_DEVICE_IP, device.getIpAddress());
        values.put(KEY_DEVICE_LASTSEEN, device.getLastSeen().toString());
        values.put(KEY_DEVICE_LOCATION, device.getLocation());
        values.put(KEY_DEVICE_OWNER, device.getOwner());
        values.put(KEY_DEVICE_PORT, device.getPort());

        return mDb.insert(DATABASE_TABLE_DEVICE, null, values);
    }
    
    /**
     * Clear and fill the venue table according to server response. Returns a
     * boolean indicating success or failure.
     * 
     * @param venues the VenueList containing all venues
     * @return true if success, false if failed
     */
    public boolean addVenues(VenueList venues) {
    	mDb.execSQL("DROP TABLE IF EXISTS "+DATABASE_TABLE_VENUE+";");
    	mDb.execSQL(DATABASE_CREATE_VENUES);
    	for (Iterator iterator = venues.iterator(); iterator.hasNext();) {
			Venue v = (Venue) iterator.next();
			ContentValues values = new ContentValues();
			values.put(KEY_VENUE_ID, v.getVenueName());
			values.put(KEY_VENUE_NAME, v.getVenueName());
			values.put(KEY_VENUE_ADDRESS, v.getVenueAddress());
			values.put(KEY_VENUE_PHONE, v.getVenuePhone());
			values.put(KEY_VENUE_TOWN, v.getVenueTown());
			values.put(KEY_VENUE_ZIP, v.getVenueZip());
			
			if ((mDb.insert(DATABASE_TABLE_VENUE, null, values)) < 0) {
				return false;
			}
		}
    	return true;
    }

    public boolean addVenueActivities(VenueList venues) {
    	mDb.execSQL("DROP TABLE IF EXISTS "+DATABASE_TABLE_ACTIVITY+";");
    	mDb.execSQL(DATABASE_CREATE_ACTIVITIES);
    	for (Iterator iterator = venues.iterator(); iterator.hasNext();) {
			VenueActivity v = (VenueActivity) iterator.next();
			ContentValues values = new ContentValues();
			values.put(KEY_ACTIVITY_NAME, v.getActivity());
			values.put(KEY_ACTIVITY_VENUEID, v.getVenue());
			values.put(KEY_ACTIVITY_MINPARTICIPANTS, v.getMinParticipants());
			values.put(KEY_ACTIVITY_MAXPARTICIPANTS, v.getMaxParticipants());
			
			if ((mDb.insert(DATABASE_TABLE_ACTIVITY, null, values)) < 0) {
				return false;
			}
		}
    	return true;
    }
    
    /**
     * Delete the note with the given rowId
     * 
     * @param rowId id of note to delete
     * @return true if deleted, false otherwise
     */
    public boolean delete(String table, long rowId) {

        return mDb.delete(table, KEY_ROWID + "=" + rowId, null) > 0;
    }

    /**
     * Return a Cursor over the list of all notes in the database
     * 
     * @return Cursor over all notes
     */
    public Cursor fetchAllDevices() {

        return mDb.query(DATABASE_TABLE_DEVICE, new String[] {KEY_ROWID, KEY_DEVICE_OWNER,
                KEY_DEVICE_LOCATION}, null, null, null, null, null);
    }
    
    public Cursor fetchAllVenues() {

        return mDb.query(DATABASE_TABLE_VENUE, new String[] {KEY_ROWID, KEY_VENUE_NAME,
                KEY_VENUE_ADDRESS}, null, null, null, null, null);
    }

    /**
     * Return a Cursor positioned at the note that matches the given rowId
     * 
     * @param rowId id of note to retrieve
     * @return Cursor positioned to matching note, if found
     * @throws SQLException if note could not be found/retrieved
     */
    public Cursor fetchDevice(long rowId) throws SQLException {

        Cursor mCursor =

                mDb.query(true, DATABASE_TABLE_DEVICE, new String[] {KEY_ROWID,
                        KEY_DEVICE_OWNER, KEY_DEVICE_ID}, KEY_ROWID + "=" + rowId, null,
                        null, null, null, null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;

    }

    /**
     * Update the note using the details provided. The note to be updated is
     * specified using the rowId, and it is altered to use the title and body
     * values passed in
     * 
     * @param rowId id of note to update
     * @param title value to set note title to
     * @param body value to set note body to
     * @return true if the note was successfully updated, false otherwise
     */
    public boolean updateDevice(long rowId, DeviceInfo device) {
        ContentValues values = new ContentValues();
        values.put(KEY_DEVICE_ID, device.getDeviceID());
        values.put(KEY_DEVICE_IP, device.getIpAddress());
        values.put(KEY_DEVICE_LASTSEEN, device.getLastSeen().toString());
        values.put(KEY_DEVICE_LOCATION, device.getLocation());
        values.put(KEY_DEVICE_OWNER, device.getOwner());
        values.put(KEY_DEVICE_PORT, device.getPort());

        return mDb.update(DATABASE_TABLE_DEVICE, values, KEY_ROWID + "=" + rowId, null) > 0;
    }
}
